-- @owner: ningyali
-- @date: 2024-08-12
-- @testpoint: 参数为不同数据类型
-- @history: modified by peilinqian at 2024-12-23:研发代码变更，IAUORL需求对数字类型重构。

--step1:建表;expect:成功
drop table if exists t_ustore_nullif_0004;
create table t_ustore_nullif_0004(
col_1 int,
col_2 decimal,
col_3 smallint,
col_4 char(30),
col_5 real,
col_6 numeric,
col_7 int,
col_8 timestamp,
col_9 clob,
col_10 number(6,2),
col_11 decimal(6,2),
col_12 varchar2(50),
col_13 varchar(30),
col_14 numeric(12,6),
col_15 date,
constraint pk_nullif_id primary key(col_1)
) with (storage_type=ustore);

--step2:建序列;expect:成功
drop sequence if exists sq_ustore_nullif_0004;
create sequence sq_ustore_nullif_0004 increment by 1 start with 100000;

--step3:插入数据;expect:成功
begin
 for i in 1..1000 loop
      insert into t_ustore_nullif_0004 values(
      sq_ustore_nullif_0004.nextval,
      3.1415926+sq_ustore_nullif_0004.nextval,
      i,
      lpad('abc','30','@'),
      3.1415926+sq_ustore_nullif_0004.nextval,
      i/4,
      i,
      to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ff'),
      lpad('abc','5000','a@123&^%djgk'),
      3.14+i,
      i+445.255,
      rpad('abc','30','&'),
      lpad('abc','30','&'),
      125563.141592,
      to_date('2019-03-1 14:58:54','yyyy-mm-dd hh24:mi:ss')
   );
    end loop;
end;
/

--step4:查询;expect:成功
select nullif('111',111);
select nullif(12.1,'12.1');
select nullif(col_5,col_8) from t_ustore_nullif_0004 order by col_5,col_8 limit 10;
select nullif(col_8,col_15) from t_ustore_nullif_0004 order by col_8,col_15 limit 10;

--step5:清理环境;expect:成功
drop table if exists t_ustore_nullif_0004;
drop sequence if exists sq_ustore_nullif_0004;